package example.dao;

import example.entity.Borrow;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 借阅记录数据访问对象
 *
 * @author 张心怡
 * @date 2024/12/16
 */
public class BorrowDao extends BaseDAO {

    public static void addBorrow(Borrow record) {
        String sql = "INSERT INTO borrow (user_id, book_id, borrow_date, due_date) VALUES (?,?,?,?)";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, record.getUserId());
            preparedStatement.setInt(2, record.getBookId());
            preparedStatement.setDate(3, new java.sql.Date(record.getBorrowDate().getTime()));
            preparedStatement.setDate(4, new java.sql.Date(record.getDueDate().getTime()));
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void deleteBorrow(int borrowId) {
        String sql = "DELETE FROM borrow WHERE borrow_id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, borrowId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void updateBorrow(Borrow record) {
        String sql = "UPDATE borrow SET return_date =? WHERE borrow_id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setDate(1, new java.sql.Date(record.getReturnDate().getTime()));
            preparedStatement.setInt(2, record.getBorrowId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Borrow getBorrowById(int borrowId) {
        Borrow record = null;
        String sql = "SELECT * FROM borrow WHERE borrow_id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);) {
            preparedStatement.setInt(1, borrowId);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    record = new Borrow();
                    record.setBorrowId(resultSet.getInt("borrow_id"));
                    record.setUserId(resultSet.getInt("user_id"));
                    record.setBookId(resultSet.getInt("book_id"));
                    record.setBorrowDate(resultSet.getDate("borrow_date"));
                    record.setDueDate(resultSet.getDate("due_date"));
                    record.setReturnDate(resultSet.getDate("return_date"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return record;
    }

    public static List<Borrow> getAllBorrows(Integer userId) {
        List<Borrow> records = new ArrayList<>();
        String sql = "SELECT * FROM borrow where 1=1 ";
        if (userId != null) {
            sql += " AND user_id = " + userId;
        }
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                Borrow record = new Borrow();
                record.setBorrowId(resultSet.getInt("borrow_id"));
                record.setUserId(resultSet.getInt("user_id"));
                record.setBookId(resultSet.getInt("book_id"));
                record.setBorrowDate(resultSet.getDate("borrow_date"));
                record.setDueDate(resultSet.getDate("due_date"));
                record.setReturnDate(resultSet.getDate("return_date"));
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    public static List<Borrow> searchBorrows(int keyword, Integer userId) {
        List<Borrow> records = new ArrayList<>();
        String sql = "SELECT * FROM borrow WHERE borrow_id  = ? ";
        if (userId != null) {
            sql += " AND user_id = " + userId;
        }
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, keyword);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    Borrow record = new Borrow();
                    record.setBorrowId(resultSet.getInt("borrow_id"));
                    record.setUserId(resultSet.getInt("user_id"));
                    record.setBookId(resultSet.getInt("book_id"));
                    record.setBorrowDate(resultSet.getDate("borrow_date"));
                    record.setDueDate(resultSet.getDate("due_date"));
                    record.setReturnDate(resultSet.getDate("return_date"));
                    records.add(record);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    public static int getBorrowedBooksCount(Integer userId) {
        String sql = "SELECT COUNT(*) FROM borrow WHERE return_date IS NULL ";
        if (userId != null) {
            sql += " AND user_id = " + userId;
        }
        try (Connection connection = getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    return resultSet.getInt(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
